package com.yzk.auction.dao;

import com.yzk.auction.entity.Category;
import com.yzk.auction.entity.FirstCategory;
import com.yzk.auction.entity.SecondCategory;
import com.yzk.auction.utils.JDBCUtils;
import com.yzk.auction.utils.PageModel;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import java.util.List;

/**
 * @author： lys
 * @date： 2020/8/20 11:16
 * @类名： SecondCategoryDao
 * @Title：
 */

public class SecondCategoryDao {
    /**
     * 查询所有二级分类
     * @return
     * @throws Exception
     */
    public PageModel findSecondCategoryAll(int page, int rows) throws Exception {
        QueryRunner runner = new QueryRunner(JDBCUtils.getDataSource());
        PageModel pageModel = null;
        List<Category> list = runner.query("SELECT second_cid,second_cname,f.first_cname FROM tb_second_category s,tb_first_category f WHERE s.first_cid=f.first_cid;\n",
                new BeanListHandler<Category>(Category.class));
        //PageModel分页处理得到分页数据及分页附加
        pageModel = new PageModel(list,page,rows);
        return pageModel;
    }

    /**
     * 根据id查信息
     * @param second_cid
     * @return
     * @throws Exception
     */
    public Category findfindSecondCategoryById(int second_cid) throws Exception {
        QueryRunner runner = new QueryRunner(JDBCUtils.getDataSource());
        Category category = runner.query("SELECT second_cid,second_cname,f.first_cname " +
                        "FROM tb_second_category s,tb_first_category f WHERE s.first_cid=f.first_cid AND second_cid=?;\n",
                new BeanHandler<Category>(Category.class),second_cid);
        return  category;
    }

    /**
     * 通过名字查询一级分类id
     * @param first_cname
     * @return
     * @throws Exception
     */
    public int findfindSecondCategoryByName(String first_cname) throws Exception {
        QueryRunner runner = new QueryRunner(JDBCUtils.getDataSource());
        FirstCategory category = runner.query("SELECT * FROM tb_first_category WHERE first_cname=?",
                new BeanHandler<FirstCategory>(FirstCategory.class),first_cname);
        return category.getFirst_cid();
    }

    /**
     * 插入二级分类信息
     * @param secondCategory
     * @throws Exception
     */
    public void insertSecondCategory(SecondCategory secondCategory) throws Exception {
        QueryRunner runner = new QueryRunner(JDBCUtils.getDataSource());
        runner.update("INSERT INTO tb_second_category VALUES(null,?,?)",
                secondCategory.getSecond_cname(),
                secondCategory.getFirst_cid());
    }

    /**
     * 删除二级分类信息
     * @param second_cid
     * @throws Exception
     */
    public void deleteSecondCategory(int second_cid) throws Exception {
        QueryRunner runner = new QueryRunner(JDBCUtils.getDataSource());
        runner.update("DELETE FROM tb_second_category WHERE second_cid=?",second_cid);
    }

    /**
     * 更新二级分类信息
     * @param secondCategory
     * @throws Exception
     */
    public void updateSecondCategory(SecondCategory secondCategory) throws Exception {
        QueryRunner runner = new QueryRunner(JDBCUtils.getDataSource());
        runner.update("UPDATE tb_second_category SET second_cname=?,first_cid=? WHERE second_cid=?",
                secondCategory.getSecond_cname(),
                secondCategory.getFirst_cid(),
                secondCategory.getSecond_cid());
    }
}
